ALTER PROC SP_CREATE_UPDATE_SALES_ORDER_LINE_ITEM_AKROS ( @ORDER_NO	VARCHAR(50),				
				@CUSTOMER_NUMBER	VARCHAR(50),
				@CUSTOMER_PO_NO		VARCHAR(50),
				@LINE_NO		INT,
				@PART_NUMBER		VARCHAR(50), --MFG Part Number
				@LINE_ITEM_QTY		INT,
				@UNIT_PRICE		NUMERIC(15,3),
				@REQUESTED_DOCK_DATE	DATETIME,
				@SCHEDULED_DATE		DATETIME,
				@BOOKING_DATE		DATETIME,
				@CUSTOMER_PART_NO	VARCHAR(50),				
				@END_CUSTOMER		VARCHAR(65),
				@PAYMENT_TERMS		VARCHAR(50), --shipping terms in SO table
				@REVISED_SCHEDULE_DATE	DATETIME,
				@PORTING_STATUS_CODE	INT OUT,
				@SO_IS_SAMPLE		INT,
				@COOL_DESCRIPTION	VARCHAR(800),
				@CUSTOMER_NAME		VARCHAR(65),
				@LINEITEM_STATUS	VARCHAR(20), --Hold
				@PO_LINE_NO		INT,
				@IS_PENDING_SHIPMENT INT,
				@SHIP_TO_TAG_NAME	VARCHAR(50),
				@SHIPPING_METHOD	VARCHAR(100),
				@SHIPPED_DATE		DATETIME,
				@SHIP_FROM_LOCATION_NAME VARCHAR(50)
			 )	

AS
BEGIN
	SET NOCOUNT ON
	--DECLARE @CUSTOMER_NAME VARCHAR(50)
	DECLARE @PRODUCT_ID INT
	DECLARE @CUSTOMER_ID INT
	DECLARE @END_CUSTOMER_ID INT
	DECLARE @SALES_ORDER_ID	INT
	DECLARE @DB_CUSTOMER_ID INT
	DECLARE @CUST_DEFAULT_BILLING_ADDRESS_ID INT
	DECLARE @CUST_DEFAULT_SHIPPING_ADDRESS_ID INT
	DECLARE @REGION_ID INT
	DECLARE @COOL_LINEITEM_ID INT
	DECLARE @CCOL_LINEITEM_ID INT
	DECLARE @SO_BOOKING_DATE DATETIME
	DECLARE @SO_SUB_TOTAL FLOAT
	DECLARE @ALARM_ID INT
	DECLARE @SHIPPING_ADDRESS_ID INT
	DECLARE @NEXT_LINE_ITEM_NO INT
	DECLARE @DUMMY_BILLING_ADDRESS_ID INT
	DECLARE @DUMMY_SHIPPING_ADDRESS_ID INT
	DECLARE @DB_C_TYPE_ID INT
	DECLARE @IS_CUSTOMER_SHIP_ADDRS INT
	DECLARE @LOCATION_ID INT
	DECLARE @BILLING_CONTACT_PERSON VARCHAR(50)
	DECLARE @SHIPPING_CONTACT_PERSON VARCHAR(50)
	DECLARE @TRANSACTION_DATE	DATETIME	

	SET @PORTING_STATUS_CODE = -1 --NOTHING 
	
	IF ( @ORDER_NO IS NULL OR LEN(RTRIM(LTRIM(@ORDER_NO))) = 0 )
	 BEGIN
		SET @PORTING_STATUS_CODE = 2 --SALES ORDER NUMBER REQUIRED
		RETURN
	 END
	--GET MFG PART NUMBER PRODUCT ID
	SET @PRODUCT_ID = NULL
	SELECT @PRODUCT_ID = PRODUCT_ID FROM PRODUCT P, PROCESS_STEP PS WHERE PART_NUMBER = @PART_NUMBER AND PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID and PS_TYPE_NAME = 'FINISHED GOODS'	
	IF ( @PRODUCT_ID IS NULL )
	BEGIN
		SET @PORTING_STATUS_CODE = 3 --NO FG PART NUMBER FOUND		 
		RETURN
	END
		
	SET @CUSTOMER_ID = NULL
	SELECT @CUSTOMER_ID = CUSTOMER_ID, @CUSTOMER_NAME = C_NAME, @CUST_DEFAULT_BILLING_ADDRESS_ID = C_DEFAULT_BILLING_ADDRESS_ID, @CUST_DEFAULT_SHIPPING_ADDRESS_ID = C_DEFAULT_SHIPPING_ADDRESS_ID FROM CUSTOMER WHERE C_NUMBER LIKE @CUSTOMER_NUMBER OR C_NUMBER LIKE '#' + @CUSTOMER_NUMBER + '%' OR C_NUMBER LIKE @CUSTOMER_NUMBER + '#%'
	IF ( @CUSTOMER_ID IS NULL )
	 BEGIN		
		SET @PORTING_STATUS_CODE = 4 --NO CUSTOMER FOUND
		RETURN
	 END
	--ELSE
	 BEGIN
		SELECT @DB_C_TYPE_ID   = C_TYPE_ID FROM CUSTOMER_TYPES WHERE C_TYPE_NAME = 'Customer'
		IF ( ( SELECT COUNT (*) FROM CUSTOMER_TYPE_RELATIONSHIP WHERE CUSTOMER_ID = @CUSTOMER_ID  AND C_TYPE_ID = @DB_C_TYPE_ID ) = 0 )		
		BEGIN
			INSERT INTO CUSTOMER_TYPE_RELATIONSHIP ( CUSTOMER_ID, C_TYPE_ID ) VALUES ( @CUSTOMER_ID, @DB_C_TYPE_ID ) 
		END
	 END

	SELECT @BILLING_CONTACT_PERSON = CONTACT_PERSON FROM ADDRESS WHERE ADDRESS_ID = @CUST_DEFAULT_BILLING_ADDRESS_ID
	
	SET @END_CUSTOMER_ID = NULL
	IF ( @END_CUSTOMER IS NOT NULL AND LEN(RTRIM(LTRIM(@END_CUSTOMER))) > 0 )
	 BEGIN
		SELECT @END_CUSTOMER_ID = CUSTOMER_ID FROM CUSTOMER WHERE C_NAME = @END_CUSTOMER
		IF ( @END_CUSTOMER_ID IS NULL )
		 BEGIN
			INSERT INTO ADDRESS(ADDRESS_1, ADDRESS_2, ADDRESS_3, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TIMESTAMP )
					VALUES ( '', '', '', '', '', '', '', '', '', '', '', '', CURRENT_TIMESTAMP )
			SELECT @DUMMY_BILLING_ADDRESS_ID = @@IDENTITY
			
			INSERT INTO ADDRESS(ADDRESS_1, ADDRESS_2, ADDRESS_3, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TIMESTAMP )
					VALUES ( '', '', '', '', '', '', '', '', '', '', '', '', CURRENT_TIMESTAMP )
			SELECT @DUMMY_SHIPPING_ADDRESS_ID = @@IDENTITY
	
			INSERT INTO CUSTOMER ( C_NAME, C_NUMBER, C_REGION, C_DEFAULT_BILLING_ADDRESS_ID, C_DEFAULT_SHIPPING_ADDRESS_ID, C_REP_OFFICE, C_CUSTOM_REQUIREMENTS, C_ECN_CHANGE_NOTIFICATION, C_DISABLED, C_Shipping_Method_1, C_Act_Number_1) 
				VALUES ( @END_CUSTOMER, @END_CUSTOMER, '', @DUMMY_BILLING_ADDRESS_ID, @DUMMY_SHIPPING_ADDRESS_ID,  '', 1, 1, 1, '', '') 
			SELECT @END_CUSTOMER_ID = @@IDENTITY	
		 END
		IF ( @END_CUSTOMER != @CUSTOMER_NAME )
		 BEGIN
			SELECT @DB_C_TYPE_ID   = C_TYPE_ID FROM CUSTOMER_TYPES WHERE C_TYPE_NAME = 'End Customer'
			IF ( ( SELECT COUNT (*) FROM CUSTOMER_TYPE_RELATIONSHIP WHERE CUSTOMER_ID = @END_CUSTOMER_ID  AND C_TYPE_ID = @DB_C_TYPE_ID ) = 0 )		
			BEGIN
				INSERT INTO CUSTOMER_TYPE_RELATIONSHIP ( CUSTOMER_ID, C_TYPE_ID ) VALUES ( @END_CUSTOMER_ID, @DB_C_TYPE_ID ) 
			END
						
			IF ( ( SELECT COUNT (*) FROM CUSTOMER_ODM_RELATIONSHIP WHERE PARENTCUSTOMER_ID = @CUSTOMER_ID  AND ODMCUSTOMER_ID = @END_CUSTOMER_ID ) = 0 )		
			BEGIN
				INSERT INTO CUSTOMER_ODM_RELATIONSHIP ( PARENTCUSTOMER_ID, ODMCUSTOMER_ID, RELATIONSHIPSTATUS_ID ) VALUES ( @CUSTOMER_ID, @END_CUSTOMER_ID, 1 ) 
			END
		 END
	 END


	SELECT TOP 1 @SHIPPING_ADDRESS_ID = A.ADDRESS_ID, @SHIPPING_CONTACT_PERSON = CONTACT_PERSON 
		FROM CUSTOMER C 
		LEFT OUTER JOIN CUSTOMER_SHIPPING_ADDRESS_TABLE CSAT 
		ON CSAT.CUSTOMER_ID = C.CUSTOMER_ID 
		JOIN ADDRESS A 
		ON A.TAG_NAME = @SHIP_TO_TAG_NAME WHERE C.CUSTOMER_ID = @CUSTOMER_ID
	IF ( @SHIPPING_ADDRESS_ID IS NULL )
	 BEGIN
		SET @PORTING_STATUS_CODE = 5 --NO SHIPPING ADDRESS FOUND
		RETURN
	 END

	IF ( @PAYMENT_TERMS IS NULL OR LEN(RTRIM(LTRIM(@PAYMENT_TERMS))) = 0 )
	BEGIN
		SET @PAYMENT_TERMS = 'Net 30'
	END
	
	IF ( @PAYMENT_TERMS IS NOT NULL AND LEN(RTRIM(LTRIM(@PAYMENT_TERMS))) > 0 )
	 BEGIN
		IF ( (SELECT COUNT(*) FROM SHIPPINGTERMS WHERE SHIPPINGTERMS = @PAYMENT_TERMS) = 0 )
		 BEGIN
			INSERT INTO SHIPPINGTERMS(SHIPPINGTERMS) VALUES(@PAYMENT_TERMS)
		 END
	 END

	IF ( @CUSTOMER_PART_NO IS NOT NULL AND LEN(RTRIM(LTRIM(@CUSTOMER_PART_NO))) > 0 )
	 BEGIN
		 BEGIN
			IF ( (SELECT COUNT(*) FROM CUSTOMER_PARTNUMBERS WHERE CUSTOMER_ID = @CUSTOMER_ID AND PRODUCT_ID = @PRODUCT_ID AND CUSTOMER_PARTNUMBER = RTRIM(LTRIM(@CUSTOMER_PART_NO)) AND ODM_ID IS NULL ) = 0 )
			 BEGIN
				INSERT INTO CUSTOMER_PARTNUMBERS(CUSTOMER_ID, PRODUCT_ID, CUSTOMER_PARTNUMBER, LABEL_NUMBER, ODM_ID) VALUES (@CUSTOMER_ID, @PRODUCT_ID, RTRIM(LTRIM(@CUSTOMER_PART_NO)), '', NULL)
			 END
		 END
	 END

	IF ( @BOOKING_DATE IS NULL )
	 BEGIN
		SET @BOOKING_DATE = CONVERT(VARCHAR,CURRENT_TIMESTAMP,101)
	 END
	IF ( @TRANSACTION_DATE IS NULL )
	 BEGIN
		SET @TRANSACTION_DATE = CURRENT_TIMESTAMP
	 END
	SET @IS_CUSTOMER_SHIP_ADDRS = 0
	/*IF ( @CUSTOMER_NAME = @END_CUSTOMER )
	 BEGIN
		SET @IS_CUSTOMER_SHIP_ADDRS = 0
	 END
	ELSE
	 BEGIN
		SET @IS_CUSTOMER_SHIP_ADDRS = 1
	 END*/
	IF ( @LINEITEM_STATUS IS NULL OR @LINEITEM_STATUS <> 'HOLD' )
	 BEGIN
		SET @LINEITEM_STATUS = 'APPROVED'
	 END
	SET @SALES_ORDER_ID = NULL
	SELECT @SALES_ORDER_ID = SALESORDER_ID, @DB_CUSTOMER_ID = CUSTOMER_ID, @SO_BOOKING_DATE = SO_BOOKING_DATE FROM SALES_ORDER WHERE SALESORDER_NO = RTRIM(LTRIM(@ORDER_NO))
	IF ( @SALES_ORDER_ID IS NULL ) --NEW SALES ORDER TO BE CREATED
	 BEGIN

		INSERT INTO Alarm (Alarm_Name, Alarm_Type, Created_By, Created_On, Expires_On, Current_State, Message, Priority, Respond_Before, Last_Updated_By, Last_Modified_Date) 
 			VALUES ('Sales Order Notification - SalesOrder Number: '+@ORDER_NO, 'SALES_ORDER', 'admin', @BOOKING_DATE, DATEADD(Month, 3, @BOOKING_DATE), 'UPDATED', '', 'HIGH', '1970-01-01 00:00:00.000', 'admin', @TRANSACTION_DATE)
		SELECT @ALARM_ID = @@IDENTITY

		INSERT INTO SALES_ORDER(SALESORDER_NO, CUSTOMER_ID, SO_CONTACT_PERSON, SO_SALES_PERSON, SO_CUSTOMER_PO_NUMBER, SO_BOOKING_DATE, SO_SHIPPING_METHOD, SO_SHIPPING_ACCOUNT, SO_BILLING_ADDRESS_ID, SO_SHIPPING_ADDRESS_ID, SO_COMMENTS, SO_SHIPPING_INSTRUCTIONS, SO_ENDUSER, SO_STATUS, SO_IS_SAMPLE, SO_IS_CANCEL, SO_IS_PENDING, SO_IS_SHIPPED, SO_ENTERED_BY_ID, SO_ENTERED_BY_NAME, ALARM_ID, TIMESTAMP, REGIONAL_REP, SO_IS_CUSTOMER_SHIP_ADDRESS, SO_FREIGHT_INSTRUCTIONS, SHIPPING_TERMS, SO_MISC_NAME, SO_SHOW_PRO_FORMA_INVOICE, SO_BILLING_CONTACT_PERSON, SO_SHIPPING_CONTACT_PERSON, SO_SALES_REP, SO_ODM_ID, SO_IS_CONSIGNMENT  )
			VALUES(RTRIM(LTRIM(@ORDER_NO)), @CUSTOMER_ID, '', '', @CUSTOMER_PO_NO, @BOOKING_DATE, ISNULL(@SHIPPING_METHOD,''), '', @CUST_DEFAULT_BILLING_ADDRESS_ID, @SHIPPING_ADDRESS_ID, '', '', '', @LINEITEM_STATUS, 1, 1, 0, 1, 'admin', 'System Administrator', @ALARM_ID, @TRANSACTION_DATE, '', @IS_CUSTOMER_SHIP_ADDRS, '', @PAYMENT_TERMS, 'Misc', 0, ISNULL(@BILLING_CONTACT_PERSON,''), ISNULL(@SHIPPING_CONTACT_PERSON,''), '', @END_CUSTOMER_ID, @SO_IS_SAMPLE )
		SELECT @SALES_ORDER_ID = @@IDENTITY

		INSERT INTO CHANGE_SALES_ORDER(SALESORDER_ID, CHANGENO, CUSTOMER_ID, CSO_CUSTOMER_PO_NUMBER, CSO_SHIPPING_METHOD, CSO_SHIPPING_ACCOUNT, CSO_BILLING_ADDRESS_ID, CSO_SHIPPING_ADDRESS_ID, CSO_STATUS, CSO_BOOKING_DATE, CSO_IS_SAMPLE, CSO_IS_CANCEL, CSO_IS_PENDING, CSO_IS_SHIPPED, CSO_ENTERED_BY_ID, CSO_ENTERED_BY_NAME, Timestamp, SHIPPING_TERMS, CSO_MISC_NAME, CSO_SHOW_PRO_FORMA_INVOICE, CSO_ODM_ID, CSO_IS_CUSTOMER_SHIP_ADDRESS)
			VALUES (RTRIM(LTRIM(@SALES_ORDER_ID)), 0, @CUSTOMER_ID, @CUSTOMER_PO_NO, ISNULL(@SHIPPING_METHOD,''), '', @CUST_DEFAULT_BILLING_ADDRESS_ID, @SHIPPING_ADDRESS_ID, @LINEITEM_STATUS, @BOOKING_DATE, 1, 1, 0, 1, 'admin', 'System Administrator', @TRANSACTION_DATE, @PAYMENT_TERMS, 'Misc', 0, @END_CUSTOMER_ID, 0 )
		SET @SO_BOOKING_DATE = CAST( @BOOKING_DATE AS DATETIME )
	 END
/*	ELSE --SALES ORDER EXISTS NEED TO UPDATE 
	 BEGIN		
		UPDATE SALES_ORDER SET SO_CUSTOMER_PO_NUMBER = @CUSTOMER_PO_NO, 
			SO_SHIPPING_METHOD = ISNULL(@SHIPPING_METHOD,''), 
			SO_BILLING_ADDRESS_ID = @CUST_DEFAULT_BILLING_ADDRESS_ID, SO_SHIPPING_ADDRESS_ID = @SHIPPING_ADDRESS_ID,
			SHIPPING_TERMS = @PAYMENT_TERMS, SO_ODM_ID = @END_CUSTOMER_ID,
			SO_IS_CUSTOMER_SHIP_ADDRESS = @IS_CUSTOMER_SHIP_ADDRS, SO_BILLING_CONTACT_PERSON = ISNULL(@BILLING_CONTACT_PERSON,''),
			SO_SHIPPING_CONTACT_PERSON = ISNULL(@SHIPPING_CONTACT_PERSON,''), SO_IS_CONSIGNMENT = @SO_IS_SAMPLE
			WHERE SALESORDER_ID = @SALES_ORDER_ID
	 END
*/
	SET @CCOL_LINEITEM_ID = NULL
	SET @NEXT_LINE_ITEM_NO = NULL

	SELECT @COOL_LINEITEM_ID = LINEITEM_ID FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE SALESORDER_ID = @SALES_ORDER_ID AND COOL_LINE_ITEM_NO = @LINE_NO
	IF ( @COOL_LINEITEM_ID IS NULL ) --NO LINEITEM PRESENT IN COOL TABLE NEED TO INSERT 
	 BEGIN
		/*
		SELECT @NEXT_LINE_ITEM_NO = MAX(COOL_LINE_ITEM_NO) FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE SALESORDER_ID = @SALES_ORDER_ID
		IF ( @NEXT_LINE_ITEM_NO IS NULL )
		 BEGIN
			SET @NEXT_LINE_ITEM_NO = @LINE_NO
		 END
		ELSE
		 BEGIN
			SET @NEXT_LINE_ITEM_NO = @NEXT_LINE_ITEM_NO + 1
		 END
		*/ 
		SET @NEXT_LINE_ITEM_NO = @LINE_NO
		INSERT INTO CUSTOMER_OPEN_ORDER_LINEITEM( SALESORDER_ID, COOL_LINE_ITEM_NO, COOL_PO_LINE_ITEM_NO, PRODUCT_ID, COOL_QTY, COOL_UNIT_COST, COOL_AMOUNT, COOL_REQUESTED_DATE, COOL_ACT_REQ_DATE, 
			COOL_BOOKING_DATE, COOL_SCHEDULE_DATE, COOL_REVISED_DATE, COOL_STATUS, COOL_ADDED_BY_ID, COOL_ADDED_BY_NAME,  TIMESTAMP, COOL_CUSTOMER_PARTNUMBER, COOL_IS_CANCEL, COOL_DESCRIPTION)
			VALUES ( @SALES_ORDER_ID, @NEXT_LINE_ITEM_NO, @PO_LINE_NO, @PRODUCT_ID, @LINE_ITEM_QTY, @UNIT_PRICE, (@LINE_ITEM_QTY*@UNIT_PRICE), @REQUESTED_DOCK_DATE, @REQUESTED_DOCK_DATE, 
			@BOOKING_DATE, @SCHEDULED_DATE, @REVISED_SCHEDULE_DATE, @LINEITEM_STATUS, 'admin', 'admin', @TRANSACTION_DATE, @CUSTOMER_PART_NO, 1, @COOL_DESCRIPTION )
		SELECT @COOL_LINEITEM_ID = @@IDENTITY
		IF ( @LINEITEM_STATUS <> 'HOLD' )
		 BEGIN
			INSERT INTO SALES_HOLD_TABLE(LINEITEM_ID, SALESORDER_ID, HOLD_REASON_CODE, HOLD_BY_GROUP, HOLD_BY_USER, IS_APPROVED, TIMESTAMP)
					VALUES(@COOL_LINEITEM_ID, @SALES_ORDER_ID, '', 'MARKETING', 'admin', 0, @TRANSACTION_DATE)
			INSERT INTO SALES_HOLD_TABLE(LINEITEM_ID, SALESORDER_ID, HOLD_REASON_CODE, HOLD_BY_GROUP, HOLD_BY_USER, IS_APPROVED, TIMESTAMP)
					VALUES(@COOL_LINEITEM_ID, @SALES_ORDER_ID, '', 'SALES', 'admin', 0, @TRANSACTION_DATE)		
			INSERT INTO SALES_HOLD_TABLE(LINEITEM_ID, SALESORDER_ID, HOLD_REASON_CODE, HOLD_BY_GROUP, HOLD_BY_USER, IS_APPROVED, TIMESTAMP)
					VALUES(@COOL_LINEITEM_ID, @SALES_ORDER_ID, '', 'FINANCE', 'admin', 0, @TRANSACTION_DATE)		
			INSERT INTO SALES_HOLD_TABLE(LINEITEM_ID, SALESORDER_ID, HOLD_REASON_CODE, HOLD_BY_GROUP, HOLD_BY_USER, IS_APPROVED, TIMESTAMP)
					VALUES(@COOL_LINEITEM_ID, @SALES_ORDER_ID, '', 'OPERATIONS', 'admin', 0, @TRANSACTION_DATE)
		 END
		ELSE
		 BEGIN
			INSERT INTO SALES_HOLD_TABLE(LINEITEM_ID, SALESORDER_ID, HOLD_REASON_CODE, HOLD_BY_GROUP, HOLD_BY_USER, IS_APPROVED, TIMESTAMP)
					VALUES(@COOL_LINEITEM_ID, @SALES_ORDER_ID, '0', 'MARKETING', 'admin', 1, @TRANSACTION_DATE)
			INSERT INTO SALES_HOLD_TABLE(LINEITEM_ID, SALESORDER_ID, HOLD_REASON_CODE, HOLD_BY_GROUP, HOLD_BY_USER, IS_APPROVED, TIMESTAMP)
					VALUES(@COOL_LINEITEM_ID, @SALES_ORDER_ID, '0', 'SALES', 'admin', 1, @TRANSACTION_DATE)		
			INSERT INTO SALES_HOLD_TABLE(LINEITEM_ID, SALESORDER_ID, HOLD_REASON_CODE, HOLD_BY_GROUP, HOLD_BY_USER, IS_APPROVED, TIMESTAMP)
					VALUES(@COOL_LINEITEM_ID, @SALES_ORDER_ID, '0', 'FINANCE', 'admin', 1, @TRANSACTION_DATE)		
			INSERT INTO SALES_HOLD_TABLE(LINEITEM_ID, SALESORDER_ID, HOLD_REASON_CODE, HOLD_BY_GROUP, HOLD_BY_USER, IS_APPROVED, TIMESTAMP)
					VALUES(@COOL_LINEITEM_ID, @SALES_ORDER_ID, '0', 'OPERATIONS', 'admin', 1, @TRANSACTION_DATE)
		 END

		INSERT INTO CUSTOMER_CHANGE_ORDER_LINEITEM( LINEITEM_ID, SALESORDER_ID, CHANGENO, CCO_LINE_ITEM_NO, PRODUCT_ID, CCO_QTY, CCO_UNIT_COST, CCO_AMOUNT, CCO_REQUESTED_DATE, 
			CCO_BOOKING_DATE, CCO_SCHEDULE_DATE, CCO_REVISED_DATE, CCO_STATUS, CCO_CHANGE_BY_ID, CCO_CHANGE_BY_NAME,  TIMESTAMP, CCO_CUSTOMER_PARTNUMBER)
			VALUES ( @COOL_LINEITEM_ID, @SALES_ORDER_ID, 0, @NEXT_LINE_ITEM_NO, @PRODUCT_ID, @LINE_ITEM_QTY, @UNIT_PRICE, (@LINE_ITEM_QTY*@UNIT_PRICE), @REQUESTED_DOCK_DATE, 
			@BOOKING_DATE, @SCHEDULED_DATE, @REVISED_SCHEDULE_DATE, @LINEITEM_STATUS, 'admin', 'admin', @TRANSACTION_DATE, @CUSTOMER_PART_NO )		

		--INSERT INTO TEMP_BACKLOG_PORTING_TABLE(SALESORDER_ID, LINEITEM_ID, LINE_ITEM_NO,SHIPMENT_NO) VALUES (@SALES_ORDER_ID, @COOL_LINEITEM_ID, @LINE_NO, @SHIPMENT_NO)
		SET @PORTING_STATUS_CODE = 0 --LINEITEM CREATED
 	 END
/*	ELSE --LINEITEM EXISTS IN COOL TABLE NEED TO UPDATE IT
	 BEGIN
		UPDATE CUSTOMER_OPEN_ORDER_LINEITEM SET PRODUCT_ID = @PRODUCT_ID, COOL_QTY = @LINE_ITEM_QTY, COOL_UNIT_COST = @UNIT_PRICE, COOL_AMOUNT = (@LINE_ITEM_QTY*@UNIT_PRICE), COOL_REQUESTED_DATE = @REQUESTED_DOCK_DATE,COOL_ACT_REQ_DATE = @REQUESTED_DOCK_DATE,
			COOL_BOOKING_DATE = @BOOKING_DATE, COOL_SCHEDULE_DATE = @SCHEDULED_DATE, COOL_REVISED_DATE = @REVISED_SCHEDULE_DATE, COOL_ADDED_BY_ID = 'admin', COOL_ADDED_BY_NAME = 'admin',
			COOL_CUSTOMER_PARTNUMBER = @CUSTOMER_PART_NO, COOL_STATUS = @LINEITEM_STATUS, [TIMESTAMP] = @TRANSACTION_DATE
			WHERE LINEITEM_ID = @COOL_LINEITEM_ID
		
		IF ( @LINEITEM_STATUS <> 'HOLD' )
		 BEGIN
			UPDATE SALES_HOLD_TABLE SET HOLD_REASON_CODE = '', IS_APPROVED = 0, TIMESTAMP = @TRANSACTION_DATE WHERE LINEITEM_ID = @COOL_LINEITEM_ID
		 END
		ELSE
		 BEGIN
			UPDATE SALES_HOLD_TABLE SET HOLD_REASON_CODE = '0', IS_APPROVED = 1, TIMESTAMP = @TRANSACTION_DATE WHERE LINEITEM_ID = @COOL_LINEITEM_ID
		 END
		SET @PORTING_STATUS_CODE = 1 --LINEITEM UPDATED
 	 END
*/	 
	--FOR SAVING the PENDING SHIPMENTS IN TEMP TABLE
	IF @IS_PENDING_SHIPMENT <= 0
	BEGIN
		--IF ( (SELECT COUNT(*) FROM Shipped_Lot WHERE SHIP_REQ_NO=@SHIP_REQ_NO  ) > 0 )
		BEGIN
			IF ( (SELECT COUNT(*) FROM Customer_Pending_Order_LineItem WHERE LINEITEM_ID=@COOL_LINEITEM_ID  ) = 0 )
			BEGIN
				INSERT INTO Customer_Pending_Order_LineItem (LINEITEM_ID,SALESORDER_ID, LINE_ITEM_NO,PRODUCT_ID,BackLog_Qty,TIMESTAMP )
				VALUES ( @COOL_LINEITEM_ID, @SALES_ORDER_ID, @LINE_NO, @PRODUCT_ID,@LINE_ITEM_QTY,@TRANSACTION_DATE )
			END
		END
	END
	--UPDATING SO SUB TOTAL
	SELECT @SO_SUB_TOTAL = SUM(ISNULL(COOL_AMOUNT,0)) FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE SALESORDER_ID = @SALES_ORDER_ID
	IF ( (SELECT COUNT(*) FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE COOL_STATUS='HOLD' AND SALESORDER_ID = @SALES_ORDER_ID AND CONVERT(VARCHAR,[TIMESTAMP],101) = CONVERT(VARCHAR,@TRANSACTION_DATE,101) ) > 0 ) --HOLD LINE ITEMS EXISTS WHICH ARE UPDATED TODAY SINCE WE ARE MAINTAINING SNAPSHOT OF THE SHEET
	 BEGIN
		UPDATE SALES_ORDER SET SO_SUB_TOTAL = ISNULL(@SO_SUB_TOTAL,0), SO_STATUS='HOLD' WHERE SALESORDER_ID = @SALES_ORDER_ID
	 END
	ELSE
	 BEGIN
		UPDATE SALES_ORDER SET SO_SUB_TOTAL = ISNULL(@SO_SUB_TOTAL,0), SO_STATUS='APPROVED' WHERE SALESORDER_ID = @SALES_ORDER_ID
	 END

    IF ( @SHIPPED_DATE IS NOT NULL AND LEN(RTRIM(LTRIM(@SHIPPED_DATE))) > 0 )
	 BEGIN
		--NEED TO GENERATE PACKING SLIP
		DECLARE @TOTAL_PS_GENERATED INT
		DECLARE @PS_LABEL VARCHAR(50)
		
		DECLARE @LOCATION_ADDRESS_ID INT
		DECLARE @FROM_ADDRESS_ID INT
		DECLARE @TO_ADDRESS_ID INT
		DECLARE @CST_BILL_ADDRESS_ID INT
		DECLARE @CUSTOMERSHIPPING_ID INT
		
		SET @SHIPPING_CONTACT_PERSON = null
		
		SELECT @TOTAL_PS_GENERATED = COUNT(*) FROM CUSTOMER_SHIPPING_TABLE WHERE SALESORDER_ID = @SALES_ORDER_ID
		SET @PS_LABEL = @ORDER_NO + '-' + CONVERT(VARCHAR,(@TOTAL_PS_GENERATED+1))
		SELECT @SHIPPING_CONTACT_PERSON = CONTACT_PERSON FROM ADDRESS WHERE ADDRESS_ID = @SHIPPING_ADDRESS_ID
		SELECT TOP 1 @LOCATION_ADDRESS_ID = ADDRESS_ID FROM LOCATION WHERE L_TYPE = 'FINISHED GOODS' AND L_VENDOR_NAME = @SHIP_FROM_LOCATION_NAME AND L_IS_DELETED = 'N'

		INSERT INTO ADDRESS(ADDRESS_1, ADDRESS_2, ADDRESS_3, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TIMESTAMP )
					SELECT ADDRESS_1, ADDRESS_2, ADDRESS_3, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TIMESTAMP FROM ADDRESS WHERE ADDRESS_ID = @LOCATION_ADDRESS_ID
		SELECT @FROM_ADDRESS_ID = @@IDENTITY

		INSERT INTO ADDRESS(ADDRESS_1, ADDRESS_2, ADDRESS_3, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TIMESTAMP )
					SELECT ADDRESS_1, ADDRESS_2, ADDRESS_3, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TIMESTAMP FROM ADDRESS WHERE ADDRESS_ID = @SHIPPING_ADDRESS_ID
		SELECT @TO_ADDRESS_ID = @@IDENTITY

		INSERT INTO ADDRESS(ADDRESS_1, ADDRESS_2, ADDRESS_3, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TIMESTAMP )
					SELECT ADDRESS_1, ADDRESS_2, ADDRESS_3, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TIMESTAMP FROM ADDRESS WHERE ADDRESS_ID = @CUST_DEFAULT_BILLING_ADDRESS_ID
		SELECT @CST_BILL_ADDRESS_ID = @@IDENTITY

		INSERT INTO CUSTOMER_SHIPPING_TABLE( LABEL, SALESORDER_ID, CST_SHIPPING_CONTACT_PERSON, CST_STATUS, CST_FROM_ADDRESS_ID, CST_TO_ADDRESS_ID,
					CST_METHOD, CST_SHIP_DATE, CST_GENERATED_BY, TIMESTAMP, INV_NO, CST_CI_SHOW_INVOICE, CST_IS_DROP_SHIPMENT,
					CST_BILLING_ADDRESS_ID, CST_LOCATION_ADDRESS_ID, CST_ODM_ID )
			 	VALUES ( @PS_LABEL, @SALES_ORDER_ID, @SHIPPING_CONTACT_PERSON, 'SHIPPED', @FROM_ADDRESS_ID, @TO_ADDRESS_ID,
					  ISNULL(@SHIPPING_METHOD,'')+'   #   ', @SHIPPED_DATE, 'admin', CURRENT_TIMESTAMP, @PS_LABEL, 0, 0,
					@CST_BILL_ADDRESS_ID, @LOCATION_ADDRESS_ID, @END_CUSTOMER_ID )

		SELECT @CUSTOMERSHIPPING_ID = @@IDENTITY
		INSERT INTO CUSTOMER_SHIPPING_LINE_ITEM_TABLE ( CUSTOMERSHIPPING_ID, LINEITEM_ID, CSLIT_QTY_SHIPPED, CSLIT_SHIPPED_DATE, CSLIT_STATUS, CSLIT_IS_DROP_SHIPMENT, INVOICE_GENERATED )
				VALUES ( @CUSTOMERSHIPPING_ID, @COOL_LINEITEM_ID, @LINE_ITEM_QTY, @SHIPPED_DATE, 'SHIPPED', 1, 0 )
				
		UPDATE CUSTOMER_OPEN_ORDER_LINEITEM SET COOL_STATUS = 'SHIPPED' WHERE LINEITEM_ID = @COOL_LINEITEM_ID

	 END
END